<?php

namespace App\Kuafu\Bak\Command;

use App\Library\FeiShu\SendFs;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Kuafu\GeneralFs\BuildFs;

class BakMySqlToChCommand extends Command
{
    protected $signature = 'bak:mysql-to-ch {startDate} {endDate}';

    protected $description = 'mysql数据转移至clickhouse';

    public function handle()
    {
        $startDate = (string)$this->argument('startDate');
        $endDate   = (string)$this->argument('endDate');

        if (!$startDate || !$endDate) {
            $this->error('请输入开始时间和结束时间');
            return;
        }

        if (!strtotime($startDate) || !strtotime($endDate)) {
            $this->error('开始时间和结束时间格式错误');
            return;
        }

        $startDate = date('Y-m-d', strtotime($startDate));
        $endDate   = date('Y-m-d', strtotime($endDate));

        $this->ask('开始时间：' . $startDate . '，结束时间：' . $endDate . '，确认执行？');
        $this->ask('开始时间：' . $startDate . '，结束时间：' . $endDate . '，二次确认？');

        $this->do($startDate, $endDate);
    }

    public function do(string $startDate, string $endDate)
    {
//        dd('功能废弃，技术留下以备参考');

        $startSeconds = time();

        $startTime = strtotime($startDate);
        $endTime   = strtotime($endDate);

        // 配置mysql数据源
        Config::set('database.connections.public', [
            'driver'         => 'mysql',
            'url'            => env('DATABASE_URL'),
            'host'           => 'rm-bp16jx32v02507gt4.mysql.rds.aliyuncs.com', // 内网ip
            'port'           => '3306',
            'database'       => 'kf_rms_order_1',
            'username'       => 'user_jilian',
            'password'       => 'kvBFGxJfLDWVgGQ6',
            'unix_socket'    => env('DB_SOCKET', ''),
            'charset'        => 'utf8mb4',
            'collation'      => 'utf8mb4_general_ci',
            'prefix'         => env('DB_PREFIX', ''),
            'prefix_indexes' => true,
            'strict'         => false,
            'engine'         => null,
            'options'        => extension_loaded('pdo_mysql') ? array_filter([
                \PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ]);

        // 备份
        $bakTables = [
            'public' => [
                'kf_order_'                   => 'clickhouseOrderAll',
                'kf_order_detail_'            => 'clickhouseOrderAll',
                'kf_order_coupon_'            => 'clickhouseOrderAll',
                'kf_order_coupon_external_'   => 'clickhouseOrderAll',
                'kf_order_discount_'          => 'clickhouseOrderAll',
                'kf_order_external_'          => 'clickhouseOrderAll',
                'kf_order_pay_union_'         => 'clickhouseOrderAll',
                'kf_order_user_info_'         => 'clickhouseOrderAll',
                'kf_pay_stream_'              => 'clickhouseOrderAll',
                'kf_waimai_orders_'           => 'clickhouseWaimaiOrderAll',
                'kf_waimai_order_details_'    => 'clickhouseWaimaiOrderAll',
                'kf_waimai_part_refund_logs_' => 'clickhouseWaimaiOrderAll',
            ],
        ];
        foreach ($bakTables as $mysqlDB => $tables) {

            dump("开始备份{$mysqlDB}库");

            foreach ($tables as $table => $chDb) {

                dump("开始备份{$table}");

                // 同步至clickhouse
                $this->syncToClickhouse($mysqlDB, $table, $startTime, $endTime, $chDb);
            }
        }

        $useSeconds = time() - $startSeconds;

        // 发送消息通知
        BuildFs::newRobot(SendFs::ROBOT_INTERNAL)
               ->setTitleContent('订单表MySQL->clickhouse备份执行完成', "{$startDate} - {$endDate}\n\n 执行耗时：{$useSeconds}秒")
               ->send();
    }

    /**
     * 同步信息至clickhouse
     * @param string $db
     * @param string $table
     * @param string $bakMonth
     * @return void
     */
    private function syncToClickhouse(string $db, string $table, int $startTime, int $endTime, string $chDB): void
    {
        // mysql配置
        $dbConfig = config("database.connections.{$db}");

        $host     = $dbConfig['host'];
        $port     = $dbConfig['port'];
        $username = $dbConfig['username'];
        $password = $dbConfig['password'];
        $database = $dbConfig['database'];
        $prefix   = $dbConfig['prefix'];

        // ch配置
        $chHost     = config("database.connections.{$chDB}.host");
        $chDatabase = config("database.connections.{$chDB}.database");

        // clickhouse table name
        $chTable = rtrim($table, '_');

        // 获取表结构
        $tableStruct = DB::connection($chDB)->select("show create table {$chTable}");
        $tableStruct = $tableStruct[0]['statement'];

        for ($i = 0; $i <= 99; $i++) {
            $mTable = $table . str_pad($i, 3, 0, STR_PAD_LEFT);

            // 替换表名
            $str = str_replace("CREATE TABLE {$chDatabase}.{$chTable}", "CREATE TABLE {$chDatabase}.tmp_{$mTable}",
                $tableStruct);

            // 替换引擎
            $str = preg_replace('/ENGINE[\s\S]*/', '', $str);
            $str .= "ENGINE = MySQL('{$host}:{$port}', '{$database}', '{$mTable}', '{$username}', '{$password}')";

            // 建表
            DB::connection($chDB)->statement($str);

            // 导入
            DB::connection($chDB)->statement("INSERT INTO {$chTable} SELECT * FROM tmp_{$mTable} where createdAt >= {$startTime} and createdAt < {$endTime};
");

            // 删除
            DB::connection($chDB)->statement("drop table tmp_{$mTable}");

            dump($mTable . ' - ch同步完成');
        }
    }
}
